﻿using MySqlConnector;
using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace WinFormsApp1
{
    internal class Mysql
    {
        //设置所读取MySQL的名称，端口，账号，密码，数据库名称
        static string s_MySQL_Login = "server=" + Profiles.GetValue("mysql", "server") + ";user=" + Profiles.GetValue("mysql", "user") + ";" +
               "database=" + Profiles.GetValue("mysql", "database") + ";port=" + Profiles.GetValue("mysql", "port") +
               ";password=" + Profiles.GetValue("mysql", "password") + ";";

        public static MySqlDataReader DB_List(string s_Table, string s_Field, string s_Where)
        {
            MySqlConnection conn = new MySqlConnection(s_MySQL_Login);
            try
            {
                conn.Open();
                //MessageBox.Show("已经建立连接");   //连接成功则显示对话框
                //string sql = "select * from work where completion_time is null; "; //sql语言
                string sql = string.Format("select {0} from {1} where {2}; ",s_Field ,s_Table,s_Where); //sql语言
                MySqlCommand cmd = new MySqlCommand(sql, conn);//执行sql语句返回数据 
                return cmd.ExecuteReader();
            }
            catch (MySqlException ex)
            {
                MessageBox.Show(ex.Message);
                return null;
            }

        }


        /// <summary>
        /// 向数据库中指定的数据表的指定行，修改字段
        /// </summary>
        /// <param name="s_Table">需要更新数据的数据表</param>
        /// <param name="s_Set">需要更新数据的字段</param>
        /// <param name="s_Where">需要更新数据的判定条件</param>
        /// <returns></returns>
        public static int DB_Update(string s_Table, string s_Set, string s_Where)
        {
            //增加数据的SQL语句
            string s_SQL_Update =
                "UPDATE " + s_Table + " " +
                "SET " + s_Set + " " +
                "WHERE " + s_Where + ";";
            //MessageBox.Show(s_SQL_Update);
            int i_rows = -1;//返回的结果
            using (MySqlConnection conn = new MySqlConnection(s_MySQL_Login))//打开数据库
            {
                using (MySqlCommand cmd = new MySqlCommand(s_SQL_Update, conn))//包含sql语句执行的结果，并提供一个方法从结果中阅读一行
                {
                    try
                    {
                        conn.Open();//打开连接
                        i_rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    catch
                    { }
                }
            }

            return i_rows;
        }

        /// <summary>
        /// 向数据库中指定的数据表增加一行数据
        /// </summary>
        /// <param name="s_Table">需要添加数据的数据表</param>
        /// <param name="s_Field">需要添加数据的字段</param>
        /// <param name="s_Data">需要添加的数据</param>
        /// <returns>返回数据添加行数</returns>
        public static int DB_Insert(string s_Table, string s_Field, string s_Data)
        {
            //增加数据的SQL语句
            string s_SQL_Insert =
                "INSERT INTO " + s_Table + "(" + s_Field + ") " +
                "VALUES (" + s_Data + ") ;";

            int i_rows = -1;//返回的结果
            using (MySqlConnection conn = new MySqlConnection(s_MySQL_Login))//打开数据库
            {
                using (MySqlCommand cmd = new MySqlCommand(s_SQL_Insert, conn))//包含sql语句执行的结果，并提供一个方法从结果中阅读一行
                {
                    try
                    {
                        conn.Open();//打开连接
                        i_rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    catch
                    { }
                }
            }

            return i_rows;
        }


        /// <summary>
        /// 向数据库中指定的数据表删除规定条件下的数据
        /// </summary>
        /// <param name="s_Table">需要删除数据的数据表</param>
        /// <param name="s_Data">需要删除的数据限定条件</param>
        /// <returns>返回数据删除行数</returns>
        public static int DB_Delete(string s_Table, string s_Data)
        {
            //增加数据的SQL语句
            string s_SQL_Insert =
                "DELETE FROM " + s_Table + " " +
                "WHERE " + s_Data + ";";
            int i_rows = -1;//返回的结果
            using (MySqlConnection conn = new MySqlConnection(s_MySQL_Login))//打开数据库
            {
                using (MySqlCommand cmd = new MySqlCommand(s_SQL_Insert, conn))//包含sql语句执行的结果，并提供一个方法从结果中阅读一行
                {
                    try
                    {
                        conn.Open();//打开连接
                        i_rows = cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                    catch
                    { }
                }
            }

            return i_rows;
        }

        public static string DB_Connection(string Mysqlconn)
        {
            MySqlConnection conn = new MySqlConnection(Mysqlconn);
            try
            {
                conn.Open();
                return "连接成功";
            }
            catch (MySqlException ex)
            {
                return ex.Message;
            }
        }



    }
}
